<?php

namespace App\Http\Controllers\Material;

use App\Http\Controllers\Controller;
use App\Models\Bus\BusDistribution;
use App\Models\Dict;
use App\Models\Material\Material;
use App\Models\Material\OutMaterial;
use App\Models\Material\OutMaterialDetail;
use App\Models\Material\PutMaterial;
use App\Models\Material\PutMaterialDetail;
use App\Models\Material\TyreOutMaterial;
use App\Models\Material\TyreOutMaterialDetail;
use App\Models\Role;
use App\Models\Workflow\Workflow;
use App\Models\Workflow\WorkflowLog;
use App\Models\Workflow\WorkflowNode;
use App\Models\Workflow\WorkflowNodeProgram;
use App\Models\Workflow\WorkflowProcess;
use App\User;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use Illuminate\Support\Facades\Storage;
use App\Http\Controllers\Material\MaterialController;

class ExportMaterialController extends Controller
{

    public $groupId;
    public $ding;

    public function __construct()
    {
        $userId = Auth::user()->id;
        $roleId = DB::table('sys_roles_users')->where('user_id', $userId)->pluck('role_id')->toArray();
        $this->groupId = $roleId;
        $this->ding=new \App\Http\Controllers\DingSendNotifyController();
    }
    /*****************************************导出统计***************************************************/
    /**
     * 导出出库统计
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportOutMaterialStati(Request $request)
    {
        $params = $request->input();
        $MaterialController=new MaterialController;
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
        ];
        $data = $MaterialController->getOutMaterialStatiList($params)
            ->groupBy('d.bus_num')
            ->get()->toArray();

        $units = Dict::getOptionsArrByName("物资单位");
        $nodes = WorkflowNode::where('workflow_id', 4)->get()->toArray();
        $out_custodian = '';
        $out_custodian_warehouse = '';
        $out_book = '';
        $out_operator = '';
        foreach ($nodes as $nv) {
            $out_signature = WorkflowLog::where('apply_key', 'akbus_material_out')
                ->where('node_id', $nv['id'])->orderBy('id', 'desc')->value('signature');
            if ($nv['node_key'] == 'out_custodian') {
                $out_custodian = '.'.parse_url($out_signature)['path'];
            }
            if ($nv['node_key'] == 'out_custodian_warehouse') {
                $out_custodian_warehouse = '.'.parse_url($out_signature)['path'];
            }
            if ($nv['node_key'] == 'out_book') {
                $out_book = '.'.parse_url($out_signature)['path'];
            }
            if (isset($params['handler_user_id']) && $params['handler_user_id'] != "undefined") {
                if ($nv['node_key'] == 'out_operator') {
                    if (count($data) > 0) {
                        $out_operator = WorkflowLog::where('apply_key', 'akbus_material_out')
                            ->where('apply_id', $data[count($data) - 1]['t_id'])
                            ->where('node_id', $nv['id'])
                            ->orderBy('id', 'desc')
                            ->value('signature');
                        $out_operator = '.'.parse_url($out_operator)['path'];
                    }
                }
            }
        }
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '出库单')
            ->setCellValue('A2', date('Y-m-d'))->setCellValue('A3', '线路')
            ->setCellValue('B3', '车号')->setCellValue('C3', '品名')->setCellValue('E3', '规格')
            ->setCellValue('G3', '单位')->setCellValue('H3', '数量')->setCellValue('J3', '单价')
            ->setCellValue('K3', '金额');

        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $num = 4;
        $rowTotalMoney = 0;
        foreach ($data as $dv) {
            $spreadsheet->getActiveSheet()->setCellValue('A'.$num, $dv['line']);
            $spreadsheet->getActiveSheet()->setCellValue('B'.$num, $dv['bus_num']);
            $spreadsheet->getActiveSheet()->setCellValue('C'.$num, $dv['title']);
            $spreadsheet->getActiveSheet()->setCellValue('E'.$num, $dv['specs']);
            $spreadsheet->getActiveSheet()->setCellValue('G'.$num, $units[$dv['unit']]);
            $spreadsheet->getActiveSheet()->setCellValue('H'.$num, $dv['total_amount']);
            $spreadsheet->getActiveSheet()->setCellValue('J'.$num, $dv['price']);
            $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $dv['total_amount'] * $dv['price']);
            $rowTotalMoney = $rowTotalMoney + $dv['total_amount'] * $dv['price'];
            $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight(25);
            for ($i = 0; $i < 11; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->getFont()->setSize(12);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            $spreadsheet->getActiveSheet()->mergeCells('C'.$num.':D'.$num)
                ->mergeCells('E'.$num.':F'.$num)->mergeCells('H'.$num.':I'.$num);
            $num++;
        }
        for ($i = 0; $i < 11; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight(50);
        $spreadsheet->getActiveSheet()->setCellValue('A'.$num, '负责人');
        if ($out_custodian&&$out_custodian!='.') {
            $drawing_B = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $drawing_B->setPath($out_custodian);
            $drawing_B->setCoordinates('B'.$num);
            $drawing_B->setWorksheet($spreadsheet->getActiveSheet());
            $drawing_B->setWidth(30)->setHeight(30)->setOffsetY(22);
        }
        $spreadsheet->getActiveSheet()->setCellValue('C'.$num, '仓库负责人');
        if ($out_custodian_warehouse&&$out_custodian_warehouse!='.') {
            $drawing_D = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $drawing_D->setPath($out_custodian_warehouse);
            $drawing_D->setCoordinates('D'.$num);
            $drawing_D->setWorksheet($spreadsheet->getActiveSheet());
            $drawing_D->setWidth(30)->setHeight(30)->setOffsetY(22);
        }
        $spreadsheet->getActiveSheet()->setCellValue('E'.$num, '出库经手人');
        if ($out_operator&&$out_operator!='.') {
            $drawing_F = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $drawing_F->setPath($out_operator);
            $drawing_F->setCoordinates('F'.$num);
            $drawing_F->setWorksheet($spreadsheet->getActiveSheet());
            $drawing_F->setWidth(30)->setHeight(30)->setOffsetY(22);
        }
        $spreadsheet->getActiveSheet()->setCellValue('H'.$num, '记账');
        if ($out_book&&$out_book!='.') {
            $drawing_I = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $drawing_I->setPath($out_book);
            $drawing_I->setCoordinates('I'.$num);
            $drawing_I->setWorksheet($spreadsheet->getActiveSheet());
            $drawing_I->setWidth(30)->setHeight(30)->setOffsetY(22);
        }
        $spreadsheet->getActiveSheet()->setCellValue('J'.$num, '合计');
        $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $rowTotalMoney);

        // 合并行、列
        $spreadsheet->getActiveSheet()->mergeCells('A1:K1')->mergeCells('A2:K2')
            ->mergeCells('C3:D3')->mergeCells('E3:F3')
            ->mergeCells('H3:I3')->mergeCells('F'.$num.':G'.$num);

        //  设置宽度
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(8.25);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(8.63);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(6);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(6);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(4.75);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(5.88);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(3.25);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(9);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(7.75);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(11.38);
        for ($i = 0; $i < 11; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        // 设置高度
        $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(28);
        $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(25);
        $spreadsheet->getActiveSheet()->getRowDimension('3')->setRowHeight(25);
        $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('A2')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C2')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);

        //设置字体
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
        $spreadsheet->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
        $spreadsheet->getActiveSheet()->getStyle('C2')->getFont()->setSize(12);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

        $spreadsheet->setActiveSheetIndex(0);
        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle('出库单统计');
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="出库单统计.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    /**
     * 导出轮胎出库统计
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportTyreOutMaterialStati(Request $request)
    {
        $params = $request->input();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
        ];
        $data = $this->getTyreOutMaterialStatiList($params)
            ->groupBy('d.bus_num')
            ->get()->toArray();

        $units = Dict::getOptionsArrByName("物资单位");
        $nodes = WorkflowNode::where('workflow_id', 5)->get()->toArray();
        $out_custodian = '';
        $out_custodian_warehouse = '';
        $out_book = '';
        $out_operator = '';
        foreach ($nodes as $nv) {
            if ($nv['node_key'] == 'tyre_out_custodian') {
                $out_custodian = WorkflowLog::where('apply_key', 'akbus_material_tyre_out')
                    ->where('node_id', $nv['id'])
                    ->orderBy('id', 'desc')->value('signature');
                $out_custodian = '.'.parse_url($out_custodian)['path'];
            }
            if ($nv['node_key'] == 'tyre_out_custodian_warehouse') {
                $out_custodian_warehouse = WorkflowLog::where('apply_key', 'akbus_material_tyre_out')
                    ->where('node_id', $nv['id'])->orderBy('id', 'desc')->value('signature');
                $out_custodian_warehouse = '.'.parse_url($out_custodian_warehouse)['path'];
            }
            if ($nv['node_key'] == 'tyre_out_book') {
                $out_book = WorkflowLog::where('apply_key', 'akbus_material_tyre_out')
                    ->where('node_id', $nv['id'])->orderBy('id', 'desc')->value('signature');
                $out_book = '.'.parse_url($out_book)['path'];
            }
            if (isset($params['handler_user_id']) && $params['handler_user_id'] != "undefined") {
                if ($nv['node_key'] == 'tyre_out__operator') {
                    if (count($data) > 0) {
                        $out_operator = WorkflowLog::where('apply_key', 'akbus_material_tyre_out')
                            ->where('apply_id', $data[count($data) - 1]['t_id'])
                            ->where('node_id', $nv['id'])
                            ->orderBy('id', 'desc')
                            ->value('signature');
                        $out_operator = '.'.parse_url($out_operator)['path'];
                    }
                }
            }
        }
        //每条线路
        $fleet = $this->getTyreOutMaterialStatiList($params)->groupBy('t.fleet')->get()->toArray();
        // $line=DB::table('akbus_bus_distribution')
        //         ->groupBy('line')->get()->toArray();

        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');

        foreach ($fleet as $k => $v) {
            if ($k > 0) {
                $spreadsheet->createSheet();
            }
            // 添加表头
            $spreadsheet->setActiveSheetIndex($k)
                ->setCellValue('A1', '出库单')
                ->setCellValue('A2', date('Y-m-d'))
                ->setCellValue('A3', '日期')->setCellValue('C3', '车号')
                ->setCellValue('E3', '线路')->setCellValue('F3', '品名')->setCellValue('H3', '规格')
                ->setCellValue('J3', '单位')->setCellValue('K3', '数量')->setCellValue('M3', '单价')
                ->setCellValue('N3', '金额');

            //居中
            $numberStyleCenter = [
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                ],
            ];
            //左
            $numberStyleLeft = [
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
                    'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                ],
            ];
            $num = 4;
            $rowTotalMoney = 0;
            foreach ($data as $dv) {
                if ($v['fleet'] == $dv['fleet']) {
                    $spreadsheet->getActiveSheet()->setCellValue('A'.$num, date('Y-m-d', strtotime($dv['created_at'])));
                    $spreadsheet->getActiveSheet()->setCellValue('C'.$num, $dv['bus_num']);
                    $spreadsheet->getActiveSheet()->setCellValue('E'.$num, $dv['line']);
                    $spreadsheet->getActiveSheet()->setCellValue('F'.$num, $dv['title']);
                    $spreadsheet->getActiveSheet()->setCellValue('H'.$num, $dv['specs']);
                    $spreadsheet->getActiveSheet()->setCellValue('J'.$num, $units[$dv['unit']]);
                    $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $dv['total_amount']);
                    $spreadsheet->getActiveSheet()->setCellValue('M'.$num, $dv['price']);
                    $spreadsheet->getActiveSheet()->setCellValue('N'.$num, $dv['total_amount'] * $dv['price']);
                    $rowTotalMoney = $rowTotalMoney + $dv['total_amount'] * $dv['price'];
                    $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight(25);
                    for ($i = 0; $i < 14; $i++) {
                        $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->getFont()->setSize(12);
                        $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
                    }
                    $spreadsheet->getActiveSheet()->mergeCells('A'.$num.':B'.$num)->mergeCells('C'.$num.':D'.$num)
                        ->mergeCells('F'.$num.':G'.$num)->mergeCells('H'.$num.':I'.$num)
                        ->mergeCells('K'.$num.':L'.$num);
                    $num++;
                }
            }
            for ($i = 0; $i < 14; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->getFont()->setSize(12);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight(59);
            $spreadsheet->getActiveSheet()->setCellValue('A'.$num, '负责人');
            if ($out_custodian&&$out_custodian!='.') {
                $drawing_B = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_B->setPath($out_custodian);
                $drawing_B->setCoordinates('B'.$num);
                $drawing_B->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_B->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('D'.$num, '仓库负责人');
            if ($out_custodian_warehouse&&$out_custodian_warehouse!='.') {
                $drawing_D = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_D->setPath($out_custodian_warehouse);
                $drawing_D->setCoordinates('E'.$num);
                $drawing_D->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_D->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('G'.$num, '出库经手人');
            if ($out_operator&&$out_operator!='.') {
                $drawing_F = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_F->setPath($out_operator);
                $drawing_F->setCoordinates('H'.$num);
                $drawing_F->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_F->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('J'.$num, '记账');
            if ($out_book&&$out_book!='.') {
                $drawing_I = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_I->setPath($out_book);
                $drawing_I->setCoordinates('K'.$num);
                $drawing_I->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_I->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('M'.$num, '合计');
            $spreadsheet->getActiveSheet()->setCellValue('N'.$num, $rowTotalMoney);

            // 合并行、列
            $spreadsheet->getActiveSheet()->mergeCells('A1:N1')->mergeCells('A2:N2')
                ->mergeCells('A3:B3')->mergeCells('C3:D3')->mergeCells('F3:G3')
                ->mergeCells('H3:I3')->mergeCells('K3:L3')
                ->mergeCells('B'.$num.':C'.$num)->mergeCells('E'.$num.':F'.$num)
                ->mergeCells('H'.$num.':I'.$num)->mergeCells('K'.$num.':L'.$num);

            //  设置宽度
            $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5.5);
            $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(6.13);
            $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(4.25);
            $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(7.88);
            $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(7.38);
            $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(5.63);
            $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(8.63);
            $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(5.38);
            $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(5.5);
            $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(6.13);
            $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(4.5);
            $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(11.25);
            $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(6.75);
            $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(8.25);
            for ($i = 0; $i < 14; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->getFont()->setSize(12);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            // 设置高度
            $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(28);
            $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(25);
            $spreadsheet->getActiveSheet()->getRowDimension('3')->setRowHeight(25);
            $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle('A2')->applyFromArray($numberStyleLeft)->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle('C2')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);

            //设置字体
            $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
            $spreadsheet->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle('C2')->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

            $spreadsheet->setActiveSheetIndex($k);
            $c = $v['fleet'].'车队';
            // 重命名 worksheet
            $spreadsheet->getActiveSheet()->setTitle($c);
        }
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="轮胎出库单.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    /**
     * 导出物资出入库统计（台账）
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportMaterialStati(Request $request)
    {
        $params = $request->input();
        $Letter = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N'];

        if (isset($params['date'])) {
            $time = strtotime($params['date']);
            $date = date('Y-m', $time);
        } else {
            $date = date('Y-m');
            $time = strtotime(date('Y-m-d'));
        }
        $start_date = date('Y-m-26', strtotime(date('Y', $time).'-'.(date('m', $time) - 1).'-26'));
        $end_date = date('Y-m-25', strtotime(date('Y', $time).'-'.(date('m', $time)).'-25'));
        $query = $this->getMaterialStatiList($params);

        $materials_data = $query['materials_query']->get()->toArray();
        $material_balance_data = $query['material_balance_query']->get()->toArray();
        $put_data = $query['put_query']->get()->toArray();
        $out_data = $query['out_query']->get()->toArray();

        $data = $this->statiTransData([
            'materials'        => $materials_data,
            'put_data'         => $put_data,
            'out_data'         => $out_data,
            'material_balance' => $material_balance_data,
        ]);

        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');

        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '安康市公共交通有限责任公司'.PHP_EOL.'车辆配件入库、消耗、出库清单（内部使用）')
            ->setCellValue('A2', $date.'('.$start_date.'-'.$end_date.')')
            ->setCellValue('A3', '序号')->setCellValue('B3', '名称')
            ->setCellValue('C3', '单位')->setCellValue('D3', '入库')->setCellValue('K3', '出库')
            ->setCellValue('N3', '结存')->setCellValue('D4', '上月结余')->setCellValue('G4', '本月入库')
            ->setCellValue('K4', '出库时间')->setCellValue('L4', '车号')->setCellValue('M4', '数量')
            ->setCellValue('N4', '数量')->setCellValue('D5', '数量')->setCellValue('E5', '单价')
            ->setCellValue('F5', '金额')->setCellValue('G5', '购买时间')->setCellValue('H5', '数量')
            ->setCellValue('I5', '单价')->setCellValue('J5', '金额');
        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        $num = 6;
        $rowTotalPrice = 0;
        $rowPutTotalPrice = 0;
        foreach ($data as $dk => $dv) {
            $spreadsheet->getActiveSheet()->setCellValue('A'.$num, $dk + 1);
            $spreadsheet->getActiveSheet()->setCellValue('B'.$num, $dv['title']);
            $spreadsheet->getActiveSheet()->setCellValue('C'.$num, $dv['unit']);
            $spreadsheet->getActiveSheet()->setCellValue('D'.$num, $dv['balance_amount']);
            $spreadsheet->getActiveSheet()->setCellValue('E'.$num, $dv['price']);
            $spreadsheet->getActiveSheet()->setCellValue('F'.$num, $dv['total_price']);
            $spreadsheet->getActiveSheet()->setCellValue('G'.$num, $dv['put_created_at']);
            $spreadsheet->getActiveSheet()->setCellValue('H'.$num, $dv['put_amount']);
            $spreadsheet->getActiveSheet()->setCellValue('I'.$num, $dv['price']);
            $spreadsheet->getActiveSheet()->setCellValue('J'.$num, $dv['put_total_price']);
            $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $dv['out_created_at']);
            $spreadsheet->getActiveSheet()->setCellValue('L'.$num, $dv['bus_num']);
            $spreadsheet->getActiveSheet()->setCellValue('M'.$num, $dv['out_amount']);
            $spreadsheet->getActiveSheet()->setCellValue('N'.$num, $dv['curr_balance_amount']);
            if ($dv['out_count'] > 1) {
                if ($dv['out_count'] == $dv['out_count_num']) {
                    $spreadsheet->getActiveSheet()->mergeCells('B'.($num - $dv['out_count_num'] + 1).':B'.$num);
                }
            }
            $rowTotalPrice = $rowTotalPrice + $dv['total_price'];
            $rowPutTotalPrice = $rowPutTotalPrice + $dv['put_total_price'];
            $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight(50);
            for ($i = 0; $i < 14; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->getFont()->setSize(12);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            $num++;
        }
        for ($i = 0; $i < 14; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()->setCellValue('B'.$num, '合计');
        $spreadsheet->getActiveSheet()->setCellValue('F'.$num, $rowTotalPrice);
        $spreadsheet->getActiveSheet()->setCellValue('J'.$num, $rowPutTotalPrice);
        $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $rowTotalPrice + $rowPutTotalPrice);

        // 合并行、列
        $spreadsheet->getActiveSheet()->mergeCells('A1:N1')->mergeCells('A2:N2')
            ->mergeCells('A3:A5')->mergeCells('B3:B5')->mergeCells('C3:C5')
            ->mergeCells('D3:J3')->mergeCells('K3:M3')->mergeCells('D4:F4')
            ->mergeCells('G4:J4')->mergeCells('K4:K5')->mergeCells('L4:L5')
            ->mergeCells('M4:M5')->mergeCells('N4:N5')->mergeCells('K'.$num.':N'.$num);

        //  设置宽度
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(3.25);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(26.5);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(6.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(6.88);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(6.88);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(8.25);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(6.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(6.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(7.5);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(11);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(7);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(15.38);
        for ($i = 0; $i < 14; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'4')->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'5')->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'4')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'5')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        // 设置高度
        $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(28);
        $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(15);
        $spreadsheet->getActiveSheet()->getRowDimension('3')->setRowHeight(15);
        $spreadsheet->getActiveSheet()->getRowDimension('4')->setRowHeight(20);
        $spreadsheet->getActiveSheet()->getRowDimension('5')->setRowHeight(20);

        //设置字体
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $spreadsheet->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
        $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);

        $spreadsheet->setActiveSheetIndex(0);

        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle('出入库台账');

        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="出入库台账.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
/************************************************************导出*******************************************************/
    /**
     * 导出入库
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportPutMaterial(Request $request)
    {
        $params = $request->input();
        $materialController=new MaterialController();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
        ];
        $nodes = WorkflowNode::where('workflow_id', 4)->get()->toArray();
        if (isset($params['node_id']) && $params['node_id'] != '-1') {
            $node_key = WorkflowNode::where('workflow_id', 4)->where('id',$params['node_id'])->value('node_key');
            if ($node_key == 'out_book') {
                $params['node_id'] = [$params['node_id'], 0];
            }
        }
        $out_id = $materialController->getPutMaterialList($params)
                    ->pluck('p.id');
        $data=PutMaterial::with(['putMaterialDetail','putMaterialDetail.material'])
                ->with([
                    'process' => function ($query) {
                        $query->where('apply_key', PutMaterial::getTableName());
                    }, 'process.node',
                ])
                ->with([
                    'logs' => function ($query) {
                        $query->where('apply_key', PutMaterial::getTableName());
                    }, 'logs.node',
                ])
                ->whereIn('id', $out_id)->get()->toArray();
        $units = Dict::getOptionsArrByName("物资单位");        
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');

        foreach ($data as $k => $v) {
            if ($k > 0) {
                $spreadsheet->createSheet();
            }
            // 添加表头
            $spreadsheet->setActiveSheetIndex($k)
                ->setCellValue('A1', '入库单')
                ->setCellValue('A2', date('Y-m-d',strtotime($v['put_time'])))
                ->setCellValue('A3', '品名')->setCellValue('C3', '规格')
                ->setCellValue('E3', '单位')->setCellValue('G3', '数量')->setCellValue('H3', '单价')
                ->setCellValue('J3', '金额');

            //居中
            $numberStyleCenter = [
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                ],
            ];
            $num = 4;
            $rowTotalMoney = 0;
            foreach ($v['put_material_detail'] as $dv) {
                $spreadsheet->getActiveSheet()->setCellValue('A'.$num, $dv['material']['title']);
                $spreadsheet->getActiveSheet()->setCellValue('C'.$num, $dv['specs']);
                $spreadsheet->getActiveSheet()->setCellValue('E'.$num, $dv['unit']?$units[$dv['unit']]:'');
                $spreadsheet->getActiveSheet()->setCellValue('G'.$num, $dv['amount']);
                $spreadsheet->getActiveSheet()->setCellValue('H'.$num, $dv['price']);
                $spreadsheet->getActiveSheet()->setCellValue('J'.$num, $dv['amount'] * $dv['price']);
                $rowTotalMoney = $rowTotalMoney + $dv['amount'] * $dv['price'];
                $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight(25);
                for ($i = 0; $i < 10; $i++) {
                    $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->getFont()->setSize(12);
                    $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
                }
                $spreadsheet->getActiveSheet()->mergeCells('A'.$num.':B'.$num)->mergeCells('C'.$num.':D'.$num)
                    ->mergeCells('E'.$num.':F'.$num)->mergeCells('H'.$num.':I'.$num);
                $num++;
            }
            for ($i = 0; $i < 10; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->getFont()->setSize(12);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight(59);
            $spreadsheet->getActiveSheet()->setCellValue('A'.$num, '负责人');

            $out_custodian = '';
            $out_custodian_warehouse = '';
            $out_book = '';
            $out_operator = '';
            foreach($v['logs'] as $lv){
                foreach ($nodes as $nv) {
                    if($lv['node_id']==$nv['id']){
                        if ($nv['node_key'] == 'put_custodian') {
                            $out_custodian = '.'.parse_url($lv['signature'])['path'];
                        }
                        if ($nv['node_key'] == 'put_custodian_warehouse') {
                            $out_custodian_warehouse = '.'.parse_url($lv['signature'])['path'];
                        }
                        if ($nv['node_key'] == 'put_book') {
                            $out_book = '.'.parse_url($lv['signature'])['path'];
                        } 
                        if ($nv['node_key'] == 'put_operator') {
                            $out_operator = '.'.parse_url($lv['signature'])['path'];
                        }
                    }
                }
            }
            if ($out_custodian&&$out_custodian!='.') {
                $drawing_B = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_B->setPath($out_custodian);
                $drawing_B->setCoordinates('B'.$num);
                $drawing_B->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_B->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('C'.$num, '仓库负责人');
            if ($out_custodian_warehouse&&$out_custodian_warehouse!='.') {
                $drawing_D = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_D->setPath($out_custodian_warehouse);
                $drawing_D->setCoordinates('D'.$num);
                $drawing_D->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_D->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('E'.$num, '入库经手人');
            if ($out_operator&&$out_operator!='.') {
                $drawing_F = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_F->setPath($out_operator);
                $drawing_F->setCoordinates('F'.$num);
                $drawing_F->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_F->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('G'.$num, '记账');
            if ($out_book&&$out_book!='.') {
                $drawing_I = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_I->setPath($out_book);
                $drawing_I->setCoordinates('H'.$num);
                $drawing_I->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_I->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('I'.$num, '合计');
            $spreadsheet->getActiveSheet()->setCellValue('J'.$num, $rowTotalMoney);

            // 合并行、列
            $spreadsheet->getActiveSheet()->mergeCells('A1:K1')->mergeCells('A2:K2')
                ->mergeCells('A3:B3')->mergeCells('C3:D3')->mergeCells('E3:F3');

            //  设置宽度
            $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(3.25);
            $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(8.63);
            $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(6);
            $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(10);
            $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(6);
            $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(4.75);
            $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(5.88);
            $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(3.25);
            $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(9);
            $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(7.75);
            $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(11.38);
            for ($i = 0; $i < 11; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->getFont()->setSize(12);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            // 设置高度
            $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(28);
            $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(25);
            $spreadsheet->getActiveSheet()->getRowDimension('3')->setRowHeight(25);
            $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle('A2')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle('C2')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);

            //设置字体
            $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
            $spreadsheet->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle('C2')->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

            $spreadsheet->setActiveSheetIndex($k);
            $c = $v['odd_num'];
            // 重命名 worksheet
            $spreadsheet->getActiveSheet()->setTitle($c);
        }
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="入库单.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
    /**
     * 导出出库
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportOutMaterial(Request $request)
    {
        $params = $request->input();
        $materialController=new MaterialController();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
        ];
        $nodes = WorkflowNode::where('workflow_id', 4)->get()->toArray();
        if (isset($params['node_id']) && $params['node_id'] != '-1') {
            $node_key = WorkflowNode::where('workflow_id', 4)->where('id',$params['node_id'])->value('node_key');
            if ($node_key == 'out_book') {
                $params['node_id'] = [$params['node_id'], 0];
            }
        }
        $out_id = $materialController->getOutMaterialList($params)
                    ->pluck('o.id');
        $data=OutMaterial::with(['outMaterialDetail','outMaterialDetail.material'])
                ->with([
                    'process' => function ($query) {
                        $query->where('apply_key', OutMaterial::getTableName());
                    }, 'process.node',
                ])
                ->with([
                    'logs' => function ($query) {
                        $query->where('apply_key', OutMaterial::getTableName());
                    }, 'logs.node',
                ])
                ->whereIn('id', $out_id)->get()->toArray();
        $units = Dict::getOptionsArrByName("物资单位");        
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');

        foreach ($data as $k => $v) {
            if ($k > 0) {
                $spreadsheet->createSheet();
            }
            // 添加表头
            $spreadsheet->setActiveSheetIndex($k)
                ->setCellValue('A1', '出库单')
                ->setCellValue('A2', date('Y-m-d',strtotime($v['out_time'])))
                ->setCellValue('A3', '车号')->setCellValue('C3', '品名')->setCellValue('E3', '规格')
                ->setCellValue('G3', '单位')->setCellValue('H3', '数量')->setCellValue('J3', '单价')
                ->setCellValue('K3', '金额');

            //居中
            $numberStyleCenter = [
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                ],
            ];
            $num = 4;
            $rowTotalMoney = 0;
            foreach ($v['out_material_detail'] as $dv) {
                $spreadsheet->getActiveSheet()->setCellValue('A'.$num, $dv['bus_num']);
                $spreadsheet->getActiveSheet()->setCellValue('C'.$num, $dv['material']['title']);
                $spreadsheet->getActiveSheet()->setCellValue('E'.$num, $dv['specs']);
                $spreadsheet->getActiveSheet()->setCellValue('G'.$num, $dv['unit']?$units[$dv['unit']]:'');
                $spreadsheet->getActiveSheet()->setCellValue('H'.$num, $dv['amount']);
                $spreadsheet->getActiveSheet()->setCellValue('J'.$num, $dv['price']);
                $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $dv['amount'] * $dv['price']);
                $rowTotalMoney = $rowTotalMoney + $dv['amount'] * $dv['price'];
                $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight(25);
                for ($i = 0; $i < 11; $i++) {
                    $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->getFont()->setSize(12);
                    $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
                }
                $spreadsheet->getActiveSheet()->mergeCells('A'.$num.':B'.$num)->mergeCells('C'.$num.':D'.$num)
                    ->mergeCells('E'.$num.':F'.$num)->mergeCells('H'.$num.':I'.$num);
                $num++;
            }
            for ($i = 0; $i < 11; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->getFont()->setSize(12);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight(59);
            $spreadsheet->getActiveSheet()->setCellValue('A'.$num, '负责人');

            $out_custodian = '';
            $out_custodian_warehouse = '';
            $out_book = '';
            $out_operator = '';
            foreach($v['logs'] as $lv){
                foreach ($nodes as $nv) {
                    if($lv['node_id']==$nv['id']){
                        if ($nv['node_key'] == 'out_custodian') {
                            $out_custodian = '.'.parse_url($lv['signature'])['path'];
                        }
                        if ($nv['node_key'] == 'out_custodian_warehouse') {
                            $out_custodian_warehouse = '.'.parse_url($lv['signature'])['path'];
                        }
                        if ($nv['node_key'] == 'out_book') {
                            $out_book = '.'.parse_url($lv['signature'])['path'];
                        } 
                        if ($nv['node_key'] == 'out_operator') {
                            $out_operator = '.'.parse_url($lv['signature'])['path'];
                        }
                    }
                }
            }
            if ($out_custodian&&$out_custodian!='.') {
                $drawing_B = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_B->setPath($out_custodian);
                $drawing_B->setCoordinates('B'.$num);
                $drawing_B->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_B->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('C'.$num, '仓库负责人');
            if ($out_custodian_warehouse&&$out_custodian_warehouse!='.') {
                $drawing_D = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_D->setPath($out_custodian_warehouse);
                $drawing_D->setCoordinates('D'.$num);
                $drawing_D->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_D->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('E'.$num, '出库经手人');
            if ($out_operator&&$out_operator!='.') {
                $drawing_F = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_F->setPath($out_operator);
                $drawing_F->setCoordinates('F'.$num);
                $drawing_F->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_F->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('H'.$num, '记账');
            if ($out_book&&$out_book!='.') {
                $drawing_I = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_I->setPath($out_book);
                $drawing_I->setCoordinates('I'.$num);
                $drawing_I->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_I->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('J'.$num, '合计');
            $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $rowTotalMoney);

            // 合并行、列
            $spreadsheet->getActiveSheet()->mergeCells('A1:K1')->mergeCells('A2:K2')
                ->mergeCells('A3:B3')->mergeCells('C3:D3')->mergeCells('E3:F3')
                ->mergeCells('H3:I3')->mergeCells('F'.$num.':G'.$num);

            //  设置宽度
            $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(3.25);
            $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(8.63);
            $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(6);
            $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(10);
            $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(6);
            $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(4.75);
            $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(5.88);
            $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(3.25);
            $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(9);
            $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(7.75);
            $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(11.38);
            for ($i = 0; $i < 11; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->getFont()->setSize(12);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            // 设置高度
            $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(28);
            $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(25);
            $spreadsheet->getActiveSheet()->getRowDimension('3')->setRowHeight(25);
            $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle('A2')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle('C2')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);

            //设置字体
            $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
            $spreadsheet->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle('C2')->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

            $spreadsheet->setActiveSheetIndex($k);
            $c = $v['odd_num'];
            // 重命名 worksheet
            $spreadsheet->getActiveSheet()->setTitle($c);
        }
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="出库单.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    /**
     * 导出轮胎出库
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportTyreOutMaterial(Request $request)
    {
        $params = $request->input();
        $materialController=new MaterialController();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
        ];
        $nodes = WorkflowNode::where('workflow_id', 5)->get()->toArray();
        if (isset($params['node_id']) && $params['node_id'] != '-1') {
            if (isset($params['node_id']) && $params['node_id'] != '-1') {
                $node_key = WorkflowNode::where('workflow_id', 5)->where('id',$params['node_id'])->value('node_key');
                if ($node_key == 'tyre_out_book') {
                    $params['node_id'] = [$params['node_id'], 0];
                }
            }
        }
        $out_id = $materialController->getTyreOutMaterialList($params)
            ->pluck('o.id')->toArray();

        $data=TyreOutMaterial::with(['tyreOutMaterialDetail','tyreOutMaterialDetail.material'])
                ->with([
                    'process' => function ($query) {
                        $query->where('apply_key', TyreOutMaterial::getTableName());
                    }, 'process.node',
                ])
                ->with([
                    'logs' => function ($query) {
                        $query->where('apply_key', TyreOutMaterial::getTableName());
                    }, 'logs.node',
                ])
                ->whereIn('id', $out_id)->get()->toArray();
        $units = Dict::getOptionsArrByName("物资单位");

        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');

        foreach ($data as $k => $v) {
            if ($k > 0) {
                $spreadsheet->createSheet();
            }
            // 添加表头
            $spreadsheet->setActiveSheetIndex($k)
                ->setCellValue('A1', '轮胎出库单')
                ->setCellValue('A2', date('Y-m-d',strtotime($v['out_time'])))
                ->setCellValue('A3', '车号')->setCellValue('C3', '品名')->setCellValue('E3', '规格')
                ->setCellValue('G3', '单位')->setCellValue('H3', '数量')->setCellValue('J3', '单价')
                ->setCellValue('K3', '金额');

            //居中
            $numberStyleCenter = [
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                ],
            ];
            $num = 4;
            $rowTotalMoney = 0;
            foreach ($v['tyre_out_material_detail'] as $dv) {
                $spreadsheet->getActiveSheet()->setCellValue('A'.$num, $dv['bus_num']);
                $spreadsheet->getActiveSheet()->setCellValue('C'.$num, $dv['material']['title']);
                $spreadsheet->getActiveSheet()->setCellValue('E'.$num, $dv['specs']);
                $spreadsheet->getActiveSheet()->setCellValue('G'.$num, $dv['unit']?$units[$dv['unit']]:'');
                $spreadsheet->getActiveSheet()->setCellValue('H'.$num, $dv['amount']);
                $spreadsheet->getActiveSheet()->setCellValue('J'.$num, $dv['price']);
                $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $dv['amount'] * $dv['price']);
                $rowTotalMoney = $rowTotalMoney + $dv['amount'] * $dv['price'];
                $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight(25);
                for ($i = 0; $i < 11; $i++) {
                    $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->getFont()->setSize(12);
                    $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
                }
                $spreadsheet->getActiveSheet()->mergeCells('A'.$num.':B'.$num)->mergeCells('C'.$num.':D'.$num)
                    ->mergeCells('E'.$num.':F'.$num)->mergeCells('H'.$num.':I'.$num);
                $num++;
            }
            for ($i = 0; $i < 11; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->getFont()->setSize(12);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight(59);
            $spreadsheet->getActiveSheet()->setCellValue('A'.$num, '负责人');

            $out_custodian = '';
            $out_custodian_warehouse = '';
            $out_book = '';
            $out_operator = '';
            foreach ($v['logs'] as $lv) {
                foreach ($nodes as $nv) {
                    if($lv['node_id']==$nv['id']){
                        if ($nv['node_key'] == 'tyre_out_custodian') {
                            $out_custodian = '.'.parse_url($lv['signature'])['path'];
                        }
                        if ($nv['node_key'] == 'tyre_out_custodian_warehouse') {
                            $out_custodian_warehouse = '.'.parse_url($lv['signature'])['path'];
                        }
                        if ($nv['node_key'] == 'tyre_out_book') {
                            $out_book = '.'.parse_url($lv['signature'])['path'];
                        }
                        if ($nv['node_key'] == 'tyre_out__operator') {
                            $out_operator = '.'.parse_url($lv['signature'])['path'];
                        }
                    }
                }
            }

            if ($out_custodian&&$out_custodian!='.') {
                $drawing_B = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_B->setPath($out_custodian);
                $drawing_B->setCoordinates('B'.$num);
                $drawing_B->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_B->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('C'.$num, '仓库负责人');
            if ($out_custodian_warehouse&&$out_custodian_warehouse!='.') {
                $drawing_D = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_D->setPath($out_custodian_warehouse);
                $drawing_D->setCoordinates('D'.$num);
                $drawing_D->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_D->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('E'.$num, '出库经手人');
            if ($out_operator&&$out_operator!='.') {
                $drawing_F = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_F->setPath($out_operator);
                $drawing_F->setCoordinates('F'.$num);
                $drawing_F->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_F->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('H'.$num, '记账');
            if ($out_book&&$out_book!='.') {
                $drawing_I = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing_I->setPath($out_book);
                $drawing_I->setCoordinates('I'.$num);
                $drawing_I->setWorksheet($spreadsheet->getActiveSheet());
                $drawing_I->setWidth(30)->setHeight(30)->setOffsetY(22);
            }
            $spreadsheet->getActiveSheet()->setCellValue('J'.$num, '合计');
            $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $rowTotalMoney);

            // 合并行、列
            $spreadsheet->getActiveSheet()->mergeCells('A1:K1')->mergeCells('A2:K2')
                ->mergeCells('A3:B3')->mergeCells('C3:D3')->mergeCells('E3:F3')
                ->mergeCells('H3:I3')->mergeCells('F'.$num.':G'.$num);

            //  设置宽度
            $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(3.25);
            $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(8.63);
            $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(6);
            $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(10);
            $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(6);
            $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(4.75);
            $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(5.88);
            $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(3.25);
            $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(9);
            $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(7.75);
            $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(11.38);
            for ($i = 0; $i < 11; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->getFont()->setSize(12);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            // 设置高度
            $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(28);
            $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(25);
            $spreadsheet->getActiveSheet()->getRowDimension('3')->setRowHeight(25);
            $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle('A2')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle('C2')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);

            //设置字体
            $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
            $spreadsheet->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle('C2')->getFont()->setSize(12);
            $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

            $spreadsheet->setActiveSheetIndex($k);
            $c = $v['odd_num'];
            // 重命名 worksheet
            $spreadsheet->getActiveSheet()->setTitle($c);
        }
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="轮胎出库单.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
}
